const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const dbConfig = {
  host: '120.78.121.135',
  port: 3306,
  user: 'root',
  password: 'Hbbd8991@',
  multipleStatements: true,
};

// 连接配置（用于创建数据库后的连接）
const dbConfigWithDB = {
  ...dbConfig,
  database: 'ticket_dev'
};

async function executeSQLFile(connection, filePath, description) {
  console.log(`\n=== 执行 ${description} ===`);
  console.log(`文件: ${filePath}`);
  
  try {
    const sql = fs.readFileSync(filePath, 'utf8');
    
    // 分割 SQL 语句（处理 DELIMITER）
    let statements = [];
    const lines = sql.split('\n');
    let currentStatement = '';
    let delimiter = ';';
    let inDelimiterBlock = false;
    
    for (let line of lines) {
      const trimmedLine = line.trim();
      
      // 跳过注释和空行
      if (!trimmedLine || trimmedLine.startsWith('--') || trimmedLine.startsWith('#')) {
        continue;
      }
      
      // 处理 DELIMITER 命令
      if (trimmedLine.startsWith('DELIMITER')) {
        delimiter = trimmedLine.split(' ')[1] || ';';
        inDelimiterBlock = delimiter !== ';';
        continue;
      }
      
      currentStatement += line + '\n';
      
      // 检查语句结束
      if (trimmedLine.endsWith(delimiter)) {
        if (delimiter === ';' || !inDelimiterBlock) {
          statements.push(currentStatement.replace(new RegExp(delimiter + '$'), ';'));
          currentStatement = '';
          if (delimiter !== ';') {
            delimiter = ';';
            inDelimiterBlock = false;
          }
        }
      }
    }
    
    // 添加最后一个语句（如果有）
    if (currentStatement.trim()) {
      statements.push(currentStatement);
    }
    
    console.log(`共找到 ${statements.length} 条SQL语句`);
    
    for (let i = 0; i < statements.length; i++) {
      const statement = statements[i].trim();
      if (statement && statement !== ';') {
        try {
          // 对于 USE 语句，使用 query 而不是 execute
          if (statement.toLowerCase().trim().startsWith('use ')) {
            await connection.query(statement);
          } else {
            await connection.execute(statement);
          }
          
          // 显示进度
          if (statement.toLowerCase().includes('create table')) {
            const match = statement.match(/create table[^`]*`([^`]+)`/i);
            console.log(`✓ 创建表: ${match ? match[1] : '未知表'}`);
          } else if (statement.toLowerCase().includes('insert into')) {
            const match = statement.match(/insert into[^`]*`([^`]+)`/i);
            console.log(`✓ 插入数据: ${match ? match[1] : '未知表'}`);
          } else if (statement.toLowerCase().includes('alter table')) {
            const match = statement.match(/alter table[^`]*`([^`]+)`/i);
            console.log(`✓ 修改表结构: ${match ? match[1] : '未知表'}`);
          } else if (statement.toLowerCase().includes('create trigger')) {
            const match = statement.match(/create trigger[^`]*`([^`]+)`/i);
            console.log(`✓ 创建触发器: ${match ? match[1] : '未知触发器'}`);
          }
        } catch (error) {
          // 忽略某些可以容忍的错误
          if (error.message.includes('already exists') || 
              error.message.includes('Duplicate entry') ||
              error.message.includes('Duplicate column name')) {
            console.log(`⚠ 跳过已存在项: ${error.message.split(':')[0]}`);
          } else {
            console.error(`❌ 执行SQL失败 (语句 ${i + 1}):`, error.message);
            console.error(`SQL: ${statement.substring(0, 100)}...`);
          }
        }
      }
    }
    
    console.log(`✅ ${description} 执行完成`);
  } catch (error) {
    console.error(`❌ 读取文件失败:`, error.message);
    throw error;
  }
}

async function main() {
  let connection;
  
  try {
    console.log('🔗 连接到数据库服务器...');
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库服务器连接成功');
    
    console.log('\n📋 显示当前数据库列表:');
    const [databases] = await connection.execute('SHOW DATABASES');
    databases.forEach(db => {
      console.log(`  - ${db.Database}`);
    });
    
    // 检查 ticket_dev 数据库是否已存在
    const ticketDevExists = databases.some(db => db.Database === 'ticket_dev');
    if (ticketDevExists) {
      console.log('\n⚠️  ticket_dev 数据库已存在，将删除并重新创建');
      await connection.execute('DROP DATABASE ticket_dev');
      console.log('✅ 已删除旧的 ticket_dev 数据库');
    }
    
    // 执行 init.sql
    const initPath = path.join(__dirname, 'sql/init.sql');
    await executeSQLFile(connection, initPath, '基础数据库结构 (init.sql)');
    
    // 关闭当前连接，使用新连接连接到 ticket_dev 数据库
    await connection.end();
    console.log('\n🔗 重新连接到 ticket_dev 数据库...');
    connection = await mysql.createConnection(dbConfigWithDB);
    console.log('✅ 已连接到 ticket_dev 数据库');
    
    // 执行 ticket_enhancement.sql
    const enhancementPath = path.join(__dirname, 'sql/ticket_enhancement.sql');
    await executeSQLFile(connection, enhancementPath, '功能增强 (ticket_enhancement.sql)');
    
    // 执行 workflow_enhancement.sql
    const workflowPath = path.join(__dirname, 'sql/workflow_enhancement.sql');
    await executeSQLFile(connection, workflowPath, '工作流增强 (workflow_enhancement.sql)');
    
    // 验证数据库结构
    console.log('\n=== 验证数据库结构 ===');
    
    const [tables] = await connection.execute('SHOW TABLES');
    console.log(`\n📊 数据库包含 ${tables.length} 张表:`);
    for (const table of tables) {
      const tableName = table[`Tables_in_ticket_dev`];
      const [rows] = await connection.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
      console.log(`  ✓ ${tableName} (${rows[0].count} 行)`);
    }
    
    // 测试基本查询
    console.log('\n=== 测试基本查询 ===');
    
    const [users] = await connection.execute('SELECT username, name, role FROM users');
    console.log('\n👥 用户列表:');
    users.forEach(user => {
      console.log(`  - ${user.username} (${user.name}) - ${user.role}`);
    });
    
    const [tickets] = await connection.execute('SELECT ticket_number, title, status FROM tickets');
    console.log('\n🎫 工单列表:');
    tickets.forEach(ticket => {
      console.log(`  - ${ticket.ticket_number}: ${ticket.title} [${ticket.status}]`);
    });
    
    const [applications] = await connection.execute('SELECT code, name FROM applications');
    console.log('\n🏢 应用列表:');
    applications.forEach(app => {
      console.log(`  - ${app.code}: ${app.name}`);
    });
    
    console.log('\n🎉 数据库创建和初始化完成！');
    console.log('\n📝 登录信息:');
    console.log('  管理员: admin / admin123');
    console.log('  支持人员: support1 / support123');
    console.log('  普通用户1: user1 / user123');
    console.log('  普通用户: user / user123');
    console.log('  操作员1: operator1 / op1234');
    console.log('  操作员: operator / op1234');
    
  } catch (error) {
    console.error('❌ 执行失败:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n🔌 数据库连接已关闭');
    }
  }
}

// 执行主函数
main().catch(console.error);